import json
import os.path

from .compBase import TBaseComp
import  threading
import requests


import pymysql
import pymssql
from DBUtils.PooledDB import PooledDB
"""
# pip install pymysql
# pip install pymssql
# pip install DBUtils==1.3  --- 特别要注意
# pip install -i https://pypi.doubanio.com/simple paho-mqtt
"""

class TCompDB(TBaseComp):
    def __init__(self):
        super().__init__()
        self.componentKey = 'db'
        self.pools = {}
        self.handlers={
            "query":self.queryByKey,
            "createRow": self.createRow,
            "updateRow": self.updateRow,
            "deleteRow": self.deleteRow,
            "viewRows": self.viewRows
        }

    def getPool(self, code):
        pool = None
        status = 0
        try:
            if code in self.pools.keys():
                pool = self.pools[code]
                status = 1
            elif code in self.settings.keys():
                settingItem = self.settings[code]
                if settingItem['dbType'] == 'mysql':
                    pool, status = self.getMysqlPool(settingItem)
                elif settingItem['dbType'] == 'mssql':
                    pool, status = self.getMssqlPool(settingItem)
                else:
                    status = 1002
            else:
                status = 1001
                print("pool Code is error.", code)
        except Exception as er:
            print(er)
        return pool, status
    def getMysqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=100,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                cursorclass= pymysql.cursor.Dictcursor,
                client_flag= pymysql.CLIENT.MULTI_STATEMENTS
            )
            self.pools[settingItem['code']] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return pool, status
    def getMssqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymssql,
                maxconnections=200,
                mincached=10,
                maxcached=15,
                maxshared=0,
                maxusage=1000,
                ping=1,
                blocking=True,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                # closeable=False,
            )
            code = settingItem['code']
            self.pools[code] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return pool, status
    def getConnection(self, code):
        conn = None
        status = 0
        try:
            pool, poolStatus = self.getPool(code)
            if poolStatus == 1:
                conn = pool.connection()
                status = 1
            else:
                status = poolStatus
        except Exception as er:
            print(er)
        return conn, status
    def query(self, code, sql, params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            conn, connstatus = self.getConnection(code)
            if connstatus == 1:
                settingItem = self.settings[code]
                dbType = settingItem['dbType']
                cursor = None
                if dbType == 'mysql':
                    cursor = conn.cursor()
                else:
                    cursor = conn.cursor(as_dict=True)
                try:
                    if params != None:
                        conn.cursorclass = pymysql.cursors.DictCursor
                        cursor.execute(sql, params)
                    else:
                        cursor.execute(sql)
                    if cursor.description != None:
                        rsList = []
                        rs = cursor.fetchall()
                        rsList.append(list(rs))
                        # queryRes['datasetList'].append(list(rs))
                        while cursor.nextset():
                            rs = cursor.fetchall()
                            rsList.append(list(rs))
                            # queryRes['datasetList'].append(list(rs))
                        queryRes['datasetList'] = rsList
                        if len(queryRes['datasetList']) > 0:
                            queryRes['recordset'] = queryRes['datasetList'][0]
                    else:
                        queryRes['executeResult'] = {
                            "rowNum": cursor.rownumber,
                            "rowCount": cursor.rowcount,
                            "newId": cursor.lastrowid
                        }
                    queryRes['queryStatus'] = 1
                except Exception as eer:
                    print('query faild.', eer)
                    queryRes['queryStatus'] = 1004
                    queryRes['errMsg'] = eer;
                cursor.close()
                cursor = None
                conn.close()
                conn = None
            else:
                queryRes['queryStatus'] = connstatus
        except Exception as er:
            print(er)
        return queryRes
    def getSql(self , dbCode ,  dbKey  ):
        sql=""
        status=0
        try:
            fnPath = os.path.join(self.settings[dbCode]['sqlPath'] , dbKey)
            fnPath = self.manager.getAbsPath(fnPath)+".txt"
            if os.path.exists(fnPath):
                sql = ""
                with open(fnPath , 'r' , encoding='utf-8') as f :
                    sql = f.read()
                status = 1
            else:
                status = - 1
        except Exception as er:
            print(er)
        return  sql , status
    def getSqlList(self , dbCode   ):
        res={}
        try:
            fnPath = os.path.join(self.settings[dbCode]['sqlPath'] , "**/*.txt")
            import glob
            import  re
            for fn in glob.glob(fnPath):
                with open(fn ,'r', encoding='utf-8') as f:
                    content = f.read()
                    params = list(set( re.findall(r'(?<=\%\().*?(?=\)s)',content)))
                    res[fn]=params
        except Exception as er:
            print(er)
        return  res

    def queryByKey(self, code, key, params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                sql , status = self.getSql(code , key )
                if status>0 :
                    queryRes = self.query(code, sql, params)
                else:
                    print('file not exists :', key)
                    queryRes['queryStatus'] = 1005
                    queryRes['errMsg'] = "key(" + key + ") is not exists."
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def createRow(self, code, table, fieldValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x for x in fieldValues.keys()])
                values = ",".join(["%(" + x + ")s" for x in fieldValues.keys()])
                sql = "insert into " + table + "(" + fields + ") values (" + values + ")"
                queryRes = self.query(code, sql, fieldValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def updateRow(self, code, table, fieldValues, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x + '=%(' + x + ')s' for x in fieldValues.keys()])
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = "update " + table + ' set ' + fields + ' where ' + keys
                ps = {}
                for k in fieldValues.keys():
                    ps[k] = fieldValues[k]
                for k in keyValues.keys():
                    ps[k] = keyValues[k]
                queryRes = self.query(code, sql, ps)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def deleteRow(self, code, table, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = "delete from  " + table + ' where ' + keys
                queryRes = self.query(code, sql, keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def viewRows(self, code, view, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                sql = "select * from  " + view
                if keyValues!=None and len(keyValues.keys())>0:
                    keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                    sql = sql  + ' where ' + keys
                queryRes = self.query(code, sql, keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes
compDB = TCompDB()
compDB.regist()


class TCompInflux(TBaseComp):
    def __init__(self):
        super().__init__()
        self.componentKey = 'influx'
        self.handlers={
            "query": self.query,
            "writePoints": self.writePoints
        }
    def writePoints(self , dbCode , protocolPoints , rp=''):
        res ={
            "status":0,
            "msg":"",
            "result":None
        }
        try:
            status = 0
            if dbCode in self.settings.keys():
                settings = self.settings[dbCode]
                url = settings['url']
                token = settings['token']
                db = settings['db']
                headers = {
                    "Authorization": token,
                    'Content-Type': 'application/json',
                }
                influxdb_url = url + '/write?db=' + db
                if rp != '':
                    influxdb_url = influxdb_url + '&rp=rePolicy_1h'
                resp = requests.post(url=influxdb_url, headers=headers, data=protocolPoints)
                if str(resp.status_code)=='204' or str(resp.status_code)=='200':
                    status = 1
                else:
                    status = 1002
            else:
                status = 1001
            res['status'] = status
        except Exception as er:
            print(er)
        return  res
    def queryPost(self , dbCode , sql  ):
        res ={
            "status":0,
            "msg":"",
            "result":None
        }
        try:
            if dbCode in self.settings.keys():
                settings = self.settings[dbCode]
                url = settings['url']
                token = settings['token']
                db = settings['db']
                headers = {
                    "Authorization": token,
                    'Content-Type': 'application/json',
                }
                influxdb_url =  url + '/query?db=' + db
                influxdb_url = influxdb_url +"&q="+sql
                resp = requests.get(url=influxdb_url , headers= headers  )
                if resp.status_code == 200 :
                    respData = json.loads( resp.content )
                    res['result'] = respData['results']
                    res['status'] = 1
            else:
                res['status'] = 1001
        except Exception as er:
            print(er)
        return  res
    def getSql(self , dbCode ,  dbKey , params):
        sql=""
        status=0
        try:
            fnPath = os.path.join(self.settings[dbCode]['sqlPath'] , dbKey)
            fnPath = self.manager.getAbsPath(fnPath)+".txt"
            if os.path.exists(fnPath):
                content = ""
                with open(fnPath , 'r' , encoding='utf-8') as f :
                    content = f.read()
                if params!=None and len(params.keys())>0:
                    content = content.format(**params)
                sql = content
                status = 1
            else:
                status = - 1
        except Exception as er:
            print(er)
        return  sql , status
    def query(self , dbCode , dbKey , params ):
        res ={
            "status":0,
            "msg":"",
            "result":None
        }
        try:
            res['result'] = self.settings
            sql , status = self.getSql(dbCode , dbKey , params)
            if status>0 :
                res = self.queryPost(dbCode  , sql )
            else:
                res['status'] = status
                res['msg']="dbKey is error"
        except Exception as er:
            print(er)
        return  res
compInflux = TCompInflux()
compInflux.regist()


